using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using CoreLab.Common;
using CoreLab.Oracle;
using System.Windows.Forms;
using Topobase.Wastewater.Isybau;

namespace ISYBAUSchemaExporter
{
    class OracleImporter
    {
        private readonly string INITIAL_TABLE = "Identifikation";
        private Dictionary<string, OracleSchemaTable> mTableDictionary;
        private Dictionary<string, string> mMappingTableList;
        private OracleConnection mConn;
        private readonly string TABLE_LIST_XMLFILE = @"E:\P4Root\isd\Dev\TB\Topobase\Developers\johnwang\ISYBAU-XML-SCHEMA\ISYBAU_EN_DE_TableNameList.xml";

        public OracleImporter(Dictionary<string, OracleSchemaTable> tableList, OracleConnection connection)
        {
            mTableDictionary = tableList;
            mMappingTableList = new Dictionary<string, string>();
            initialTableMapList(TABLE_LIST_XMLFILE);
            mConn = connection;
        }

        private bool initialTableMapList(string tableListXmlFile)
        {
            return false;
        }

        private OracleConnection InitialOracleConnection(string service, string name, string password)
        {
            try
            {
                string connString = string.Format("User Id={0};Password={1};Data Source={2}", name, password, service);
                OracleConnection conn = new OracleConnection(connString);
                if (conn.State != System.Data.ConnectionState.Open)
                {
                    conn.Open();
                }
                return conn;
            }
            catch (Exception ex)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, ex.Message);
                errorMessage = String.Concat(errorMessage, " Inner: ");
                errorMessage = String.Concat(errorMessage, ex.InnerException.Message);

                MessageBox.Show(errorMessage, "Error");
            }
            return null;
        }

        public int Import(Identifikation item, object context)
        {
            int result = -1;
            string tableName = GetTableNameFromObject(item);
            if (mTableDictionary.ContainsKey(tableName))
            {
                OracleSchemaTable tableDefinition = mTableDictionary[tableName];
                result = ImportTable(item, tableDefinition, null);
            }
            return result;
        }

        private int ImportTable(object item, OracleSchemaTable tableDefinition, object context)
        {
            int result = -1;
            Type itemType = item.GetType();


            Dictionary<string, string> fieldsPair = new Dictionary<string, string>();
            List<string> fieldsList = new List<string>();

            // AUTO_ID field
            int autoID = GetLastAutoID(tableDefinition.DatabaseName);
            if (autoID != -1)
            {
                fieldsPair.Add(OracleSchemaVisitor.FIELD_AUTO_ID, autoID.ToString());

                if (tableDefinition is OracleSchemaGeomTable)
                {
                    // Handle Geometry data insert statement
                    string propName = tableDefinition.Fields[1].DatabaseName;
                    string PropValue = GenerateGeomStatement(item, tableDefinition, context);
                    fieldsPair.Add(propName, PropValue);
                }
                else
                {   // handle the other common table
                    if (tableDefinition.Name.Equals("Knoten"))
                    {
                        // insert one record in the table
                        OracleSchemaCollectionField geomCollectionField = tableDefinition.Fields[1] as OracleSchemaCollectionField;
                        if (geomCollectionField != null)
                        {
                            FillCollectionField(geomCollectionField, item, autoID);
                        }
                    }
                    else
                    {
                        // the other fields from object public property
							  if (itemType.IsArray && tableDefinition.Fields.Count == 2 && tableDefinition.Fields[1] is OracleSchemaCollectionField)
							  {
								  OracleSchemaCollectionField singleColField = tableDefinition.Fields[1] as OracleSchemaCollectionField;
								  object singleColFieldVal = FillCollectionField(singleColField, item, autoID);
								  //fieldsPair.Add(singleColField.DatabaseName, singleColFieldVal.ToString());
							  }
							  else
							  {
								  int index = 0;
								  foreach (PropertyInfo property in itemType.GetProperties())
								  {
									  index++;

									  OracleSchemaField propertyField = GetFieldFromTable(tableDefinition, property);

									  if (propertyField == null && property.Name.Contains("Item"))
									  {
										  // Collection items
										  Object itemValue = itemType.InvokeMember(property.Name, BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty, null, item, null);
										  if (itemValue != null)
										  {
											  propertyField = GetChoiceFieldFromTable(tableDefinition, property, itemValue);
											  if (propertyField == null)
											  {
												  propertyField = GetChoiceFieldByIndex(tableDefinition, index);
											  }
										  }
									  }
									  if (propertyField != null)
									  {
										  Object propertyValue = itemType.InvokeMember(property.Name, BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty, null, item, null);
										  if (propertyField.Nullable && propertyValue == null)
											  continue;

										  Object fieldValue = FillField(propertyField, propertyValue, autoID);
										  if (fieldValue is Boolean)
										  {
											  int boolValue = ((Boolean)fieldValue ? 1 : 0);
											  fieldsPair.Add(propertyField.DatabaseName, boolValue.ToString());
										  }
										  else
										  {
											  if (propertyField is OracleSchemaChoiceField)
											  {
												   Type propType = propertyValue.GetType();
													OracleSchemaChoiceField choiceField = propertyField as OracleSchemaChoiceField;
												  if (fieldValue is string)
												  {
													  fieldsPair.Add(choiceField.TypeFieldName, fieldValue.ToString());
													  fieldsPair.Add(choiceField.ItemFieldName, "0");
												  }
												  else
												  {													 
													  fieldsPair.Add(choiceField.TypeFieldName, propType.Name);
													  fieldsPair.Add(choiceField.ItemFieldName, fieldValue.ToString());
												  }
											  }
											  else
											  {
												  fieldsPair.Add(propertyField.DatabaseName, fieldValue.ToString());
											  }
										  }
										  fieldsList.Add(propertyField.DatabaseName);
									  }
								  }
							  }
                    }
                    // check if any not nullable fields without any specified values
                    foreach (OracleSchemaField field in tableDefinition.Fields)
                    {
                        if (!field.Nullable && !fieldsList.Contains(field.DatabaseName))
                        {
                            if (field.DatabaseName.Contains("HAS_"))
                            {
                                fieldsPair.Add(field.DatabaseName, "1");
                            }
                        }
                    }
                }
                string insertStatement = string.Format("INSERT INTO {0}", tableDefinition.DatabaseName);
                string fieldDec = string.Empty;
                string fieldVal = string.Empty;

                foreach (KeyValuePair<string, string> kvp in fieldsPair)
                {
                    fieldDec += string.Format("{0},", kvp.Key);
                    if (kvp.Key.StartsWith("GEOM"))
                    {
                        fieldVal += string.Format("{0},", kvp.Value);
                    }
                    else
                    {
                        fieldVal += string.Format("'{0}',", kvp.Value);
                    }
                }

                fieldDec = fieldDec.Substring(0, fieldDec.Length - 1);
                fieldVal = fieldVal.Substring(0, fieldVal.Length - 1);
                insertStatement += string.Format(" ({0}) VALUES ({1})", fieldDec, fieldVal);
                try
                {
                    OracleCommand cmd = new OracleCommand(insertStatement, mConn);
                    int isSuccess = cmd.ExecuteNonQuery();
                    if (isSuccess == 1)
                    {
                        result = autoID;
                    }
                }
                catch (Exception ex)
                {
                    String errorMessage;
                    errorMessage = "Error: ";
                    errorMessage = String.Concat(errorMessage, ex.Message);
                    errorMessage = String.Concat(errorMessage, " Inner: ");
                    errorMessage = String.Concat(errorMessage, ex.InnerException.Message);

                    MessageBox.Show(errorMessage, "Error");
                }
            }

            return result;
        }

        private object FillField(OracleSchemaField field, object fieldValue, object context)
        {
            object result = null;

            if (field is OracleSchemaCollectionField)
            {
                result = FillCollectionField(field as OracleSchemaCollectionField, fieldValue, context);
            }
            else if (field is OracleSchemaDomainField)
            {
                result = FillDomainField(field as OracleSchemaDomainField, fieldValue, context);
            }
            else if (field is OracleSchemaReferenceField)
            {
                result = FillReferenceField(field as OracleSchemaReferenceField, fieldValue, context);
            }
            else if (field is OracleSchemaChoiceField)
            {
                result = FillChoiceField(field as OracleSchemaChoiceField, fieldValue, context);
            }
            else if (field is OracleSchemaGeomField)
            {
                result = FillGeomField(field as OracleSchemaGeomField, fieldValue, context);
            }
            else
            {
                result = fieldValue;
            }
            return result;
        }

        private object FillDomainField(OracleSchemaDomainField field, object fieldValue, object context)
        {
            int result = -1;
            string enuValue = fieldValue.ToString();
            if (enuValue.Contains("Item"))
            {
                enuValue = enuValue.Substring(4, enuValue.Length - 4);
            }
            OracleSchemaTable table = field.ReferencedTable;
            if (table != null)
            {
                string query = string.Format("SELECT {0} FROM {1} WHERE {2}='{3}'", OracleSchemaDomainTable.FIELD_KEY, table.DatabaseName, OracleSchemaDomainTable.FIELD_SHORTVALUE, enuValue);
                OracleCommand cmd = new OracleCommand(query, mConn);
                OracleDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result = reader.GetInt32(0);
                }
            }
            return result;
        }

        private object FillCollectionField(OracleSchemaCollectionField field, object fieldValue, object context)
        {
            int result = 0;
            OracleSchemaTable collectionTable = field.CollectionTable;
            OracleSchemaTable referencedTable = field.ReferencedTable;


            if (referencedTable.DatabaseName.ToUpper().Equals("AUFTRAEGE") || referencedTable.DatabaseName.ToLower().Equals("stammdatentype"))
            {
                bool debug = true;
            }

            int pid = (int)context;

            Type valueType = fieldValue.GetType();
            if (valueType.IsArray)
            {
                int length = (int)valueType.InvokeMember("Length", BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance, null, fieldValue, null);
                if (length > 0)
                {
                    try
                    {
                        for (int i = 0; i < length; i++)
                        {
                            object[] args = { i };
                            object item = valueType.InvokeMember("GetValue", BindingFlags.Public | BindingFlags.Instance | BindingFlags.InvokeMethod, null, fieldValue, args);
                            if (item != null)
                            {
                                int autoID = GetLastAutoID(collectionTable.DatabaseName);
                                int childID = ImportTable(item, referencedTable, null);
                                string insertQuery = string.Format("INSERT INTO {0} ({1}, {2}, {3}) VALUES ('{4}', '{5}', '{6}')", collectionTable.DatabaseName, collectionTable.Fields[0].DatabaseName, collectionTable.Fields[1].DatabaseName, collectionTable.Fields[2].DatabaseName, autoID, pid, childID);
                                OracleCommand cmd = new OracleCommand(insertQuery, mConn);
                                cmd.ExecuteNonQuery();
                            }
                        }
                        result = 1;
                    }
                    catch (Exception ex)
                    {
                        string message = ex.Message;
                        MessageBox.Show(message);
                    }
                }
            }

            return result;
        }

        private object FillReferenceField(OracleSchemaReferenceField field, object fieldValue, object context)
        {
            return ImportTable(fieldValue, field.ReferencedTable, context);
        }

        private object FillChoiceField(OracleSchemaChoiceField field, object fieldValue, object context)
        {
            int result = -1;
				if (fieldValue is String)
				{
					// there existing some simple choice type, which contain same type (int, string or the mixed)
					// for these cases, we just return its value
					return fieldValue;
				}
				else
				{
					Dictionary<string, string> choiceDictionary = field.ChoicedTypeList;
					Type fieldType = fieldValue.GetType();
					if (choiceDictionary.ContainsValue(fieldType.Name))
					{
						string tableName = fieldType.Name;
						OracleSchemaTable table = mTableDictionary[tableName];
						if (table != null)
						{
							result = ImportTable(fieldValue, table, null);
						}
					}
				}
            return result;
        }

        private object FillGeomField(OracleSchemaGeomField field, object fieldValue, object context)
        {
            string name = field.Name;

            return fieldValue;
        }

        private string GenerateGeomStatement(object item, OracleSchemaTable table, object context)
        {
            Type type = item.GetType();
            string propX = "Rechtswert";
            string propY = "Hochwert";
            string propZ = "Punkthoehe";

            double[] coords = GetCoordFromObject(item, propX, propY, propZ);
            return string.Format("SDO_GEOMETRY(2001, NULL,SDO_POINT_TYPE({0}, {1}, {2}),NULL, NULL)", coords[0].ToString(), coords[1].ToString(), coords[2].ToString());
        }

        private double[] GetCoordFromObject(object xmlData, string propX, string propY, string propZ)
        {
            double[] result = { 0.0, 0.0, 0.0 };
            Type itemType = xmlData.GetType();

            Object valueX = itemType.InvokeMember(propX, BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty, null, xmlData, null);
            if (valueX != null)
            {
                result[0] = decimal.ToDouble((decimal)valueX);
            }

            Object valueY = itemType.InvokeMember(propY, BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty, null, xmlData, null);
            if (valueY != null)
            {
                result[1] = decimal.ToDouble((decimal)valueY);
            }

            Object valueZ = itemType.InvokeMember(propY, BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty, null, xmlData, null);
            if (valueZ != null)
            {
                result[2] = decimal.ToDouble((decimal)valueZ);
            }

            return result;
        }
        private int GetLastAutoID(string table)
        {
            int result = -1;
            try
            {
                string query = string.Format("SELECT count(*) AS LAST_AUTO_ID FROM {0}", table);
                OracleCommand cmd = new OracleCommand(query, mConn);
                OracleDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result = reader.GetInt32(0) + 1;
                }
            }
            catch (Exception ex)
            {
                string message = ex.Message;
                MessageBox.Show(message);
            }
            return result;
        }

        private string GetTableNameFromObject(object item)
        {
            return INITIAL_TABLE;
        }

        private OracleSchemaField GetFieldFromTable(OracleSchemaTable table, PropertyInfo property)
        {
            foreach (OracleSchemaField field in table.Fields)
            {
                if (field is OracleSchemaChoiceField)
                {
                    OracleSchemaChoiceField choiceField = field as OracleSchemaChoiceField;
                    Dictionary<string, string> optionTableList = choiceField.ChoicedTypeList;
                    if (optionTableList.ContainsKey(property.Name))
                    {
                        return field;
                    }
                }
                else if (property.Name.Equals(field.Name))
                {
                    return field;
                }
            }
            return null;
        }

        private OracleSchemaChoiceField GetChoiceFieldFromTable(OracleSchemaTable table, PropertyInfo property, object propertyValue)
        {
            Type propType = propertyValue.GetType();
            foreach (OracleSchemaField field in table.Fields)
            {
                if (field is OracleSchemaChoiceField)
                {
                    OracleSchemaChoiceField choiceField = field as OracleSchemaChoiceField;
                    Dictionary<string, string> optionTableList = choiceField.ChoicedTypeList;
                    if (optionTableList.ContainsValue(propType.Name))
                    {
                        return choiceField;
                    }
                }
            }
            return null;
        }

		 private OracleSchemaChoiceField GetChoiceFieldByIndex(OracleSchemaTable table, int index)
		 {
			 foreach (OracleSchemaField field in table.Fields)
			 {
				 if (field is OracleSchemaChoiceField)
				 {
					 OracleSchemaChoiceField choiceField = field as OracleSchemaChoiceField;
					 if (choiceField.PropertyIndex == index)
					 {
						 return choiceField;
					 }
				 }
			 }
			 return null;
		 }
    }
}
